﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaintenanceGetServerInformation]
#-- Purpose:		Retrieve server information to store in the DBA Warehouse
#--	Last Update:	04/17/2012
#--					For a complete history - please review comments in Version
#--					Control.
#--	Readings:		
#--	http://dbamohsin.wordpress.com/2011/02/16/retrieve-the-sql-service-accounts-using-t-sql/
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaintenanceGetServerInformation]
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @backup_dir varchar(4000), @service_name nvarchar(255), @sql_service nvarchar(255), @agent_service nvarchar(255)

--- Get the Backup Directory
EXEC	dbo.spGetBackupDirectory @backup_directory_name = @backup_dir OUTPUT

--- Get SQL Server service account information
IF CHARINDEX('\', CAST(SERVERPROPERTY('ServerName') AS varchar)) = 0
	SELECT	@service_name = 'SYSTEM\CurrentControlSet\Services\MSSQLSERVER'
ELSE 
	SELECT	@service_name = 'SYSTEM\CurrentControlSet\Services\MSSQL$' + RIGHT(CAST(SERVERPROPERTY('ServerName') AS varchar), LEN(CAST(SERVERPROPERTY('ServerName') AS varchar)) - CHARINDEX('\',CAST(SERVERPROPERTY('ServerName') AS varchar),1))
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @service_name, N'ObjectName', @sql_service OUTPUT

--- Get SQL Server Agent service account information
IF CHARINDEX('\', CAST(SERVERPROPERTY('ServerName') AS varchar)) = 0
	SELECT	@service_name = 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
ELSE 
	SELECT	@service_name = 'SYSTEM\CurrentControlSet\Services\SQLAGENT$' + RIGHT(CAST(SERVERPROPERTY('ServerName') AS varchar), LEN(CAST(SERVERPROPERTY('ServerName') AS varchar)) - CHARINDEX('\',CAST(SERVERPROPERTY('ServerName') AS varchar),1))
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @service_name, N'ObjectName', @agent_service OUTPUT

--- Return the server information
SELECT		SERVERPROPERTY('ServerName') server_name, 
			SERVERPROPERTY('EngineEdition') engine_edition, 
			(CASE	WHEN SERVERPROPERTY('EditionID') IS NOT NULL THEN SERVERPROPERTY('EditionID')
					WHEN SERVERPROPERTY('EngineEdition') = 1 THEN -1253826760
					WHEN SERVERPROPERTY('EngineEdition') = 2 THEN -1534726760
					WHEN SERVERPROPERTY('EngineEdition') = 3 THEN 1804890536
					ELSE 0
					END) edition_id, 
			SERVERPROPERTY('ProductVersion') product_version, 
			SERVERPROPERTY('ProductLevel') product_level,
			ISNULL(CAST(CAST(SUM(CASE WHEN sc.name = 'Jobs from MSX' THEN 1 ELSE 0 END) AS bit) as int), 0) has_master_jobs,
			ISNULL(CAST(CAST(SUM(CASE WHEN sc.name = 'Jobs from MSX' THEN 0 ELSE 1 END) AS bit) as int), 0) has_local_jobs,
			@backup_dir backup_directory,
			ISNULL(CAST(CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS bit) as int), 0) integrated_only,
			@sql_service sql_service,
			@agent_service agent_service
FROM		msdb.dbo.sysjobs sj
JOIN		msdb.dbo.syscategories sc ON sj.category_id = sc.category_id
WHERE		sj.enabled = 1

SET NOCOUNT OFF